package com.awspaas.user.apps.cpq.controller;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import com.actionsoft.bpms.bo.engine.BO;
import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.commons.mvc.view.ResponseObject;
import com.actionsoft.bpms.server.UserContext;
import com.actionsoft.bpms.util.DBSql;
import com.actionsoft.sdk.local.SDK;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class CPQ_GetAllModuleNumberWeb {

	public String getAllModuleNumber(UserContext userContext, String bindId, String cs, String datas) {
		// 将数组转换为列表
		// JSONArray list=JSONObject.parseArray(cs);		
		//JSONArray mList=JSONObject.parseArray(datas);	
		// 获取模块所需参数信息
		Map<String, JSONObject> pMap = new HashMap<String, JSONObject>();
		Map<String, String> nameMap = new HashMap<String, String>();
		Map<String, String> noMap = new HashMap<>();
		List<BO> ps=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS").bindId(bindId).list();
		List<BO> dataBO=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_GRIDS").bindId(bindId).list();
		if(ps.size()>0) {
			for (int i = 0; i < ps.size(); i++) {
				//JSONObject json = (JSONObject) list.get(i);
				BO json =  ps.get(i);
				String bh = json.getString("PARMENTIER_NO");
				String value =  json.getString("PARMENTIER_VALUE");
				String name =  json.getString("PARMENTIER_NAME");
				String dw =  json.getString("DW");
				JSONObject jsonObject=new JSONObject();
				//判断value是否为数据
				if( NumberUtils.isCreatable(value)) {
					jsonObject.put("type", "1");//值为1者表示value为数值
				}else {
					jsonObject.put("type", "0");//值为0者表示value为字符串
				}
				jsonObject.put("value", value);
				pMap.put(bh, jsonObject);
				nameMap.put(bh, name);
				int num = i + 1;
				//noMap.put(bh, "B$" + num);
			}
			System.out.println("pMap:"+pMap.toString());
		}

		// 获取模块编码信息
		Map<String, BO> idMap = new HashMap<>();
		String bms = "";
		for (BO json : dataBO) {
			//JSONObject json = (JSONObject) string;
			String bm = json.getString("MKBH");
			if (bms.isEmpty()) {
				bms = "'" + bm + "'";
			} else {
				bms += ",'" + bm + "'";
			}
			String id = json.getString("ID");
			idMap.put(id, json);
		}
		/**
		 * 获取物料中制造件和装配件的原材料信息，用于原材料重量的计算
		 */
		String  clsql="select a.BM,a.MC,a.LX,a.XH,a.ZL,b.BM as BH from   BO_EU_CPQ_COMPONENT_PART_CLYL a,BO_EU_CPQ_COMPONENT_PART b"
				+ " where a.BINDID=b.BINDID and  b.TYPE in('装配件','制造件')";
		List<RowMap> clmaps=DBSql.getMaps(clsql);
		//1、将所涉及的装配件和制造件的材料重量信息存放在map中。
		Map<String, List<RowMap>> aboutCPMap = new HashMap<>();
		if (clmaps.size() > 0) {
			for (RowMap rowMap : clmaps) {
				String bh = rowMap.getString("BH");
				if (aboutCPMap.containsKey(bh)) {
					List<RowMap> lists = aboutCPMap.get(bh);
					lists.add(rowMap);
					aboutCPMap.put(bh, lists);
				} else {
					List<RowMap> lists = new ArrayList<RowMap>();
					lists.add(rowMap);
					aboutCPMap.put(bh, lists);
				}

			}
		}
		//用于存放计算后的材料重量
		Map<String, JSONObject> zclMaps = new HashMap<>();
		
		String sql = "select  * from  BO_EU_CPQ_MODULE_MSG where   STATUS='启用' and MKBM in (" + bms + ")";
		List<RowMap> maps = DBSql.getMaps(sql);
		Map<String, RowMap> moduleMap = new HashMap<>();
		if (maps.size() > 0) {
			for (RowMap rowMap : maps) {
				String mkbm = rowMap.getString("MKBM");
				moduleMap.put(mkbm, rowMap);
			}
		}
		// 对模块数量和模块单价进行计算
		List<BO> bos = SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_GRIDS").bindId(bindId).list();
		//存放已经保存的数据
		Map<String, BO> boMap = new HashMap<>();
		for (BO bo : bos) {
			boMap.put(bo.getString("ID"), bo);
		}
		List<String> sqlList=new ArrayList<>();
		List<BO> sqlLists=new ArrayList<>();
		List<String> zfLists=new ArrayList<>();
		for (String  id : idMap.keySet()) {
			//存放当前模块中所涉及的材料重量信息
			Map<String, JSONObject> clMaps = new HashMap<>();
			Workbook workbookwl = new XSSFWorkbook();
			Workbook workbook = new XSSFWorkbook();
			try {	
				// 获取对应编码的数据信息；
				if (idMap.containsKey(id)) {
					BO json = idMap.get(id);
					String bm = json.getString("MKBH");
					System.out.println("模块名称："+json.getString("XDXH")+"，编码："+bm);
					// 获取模块维护台账的模块数据
					RowMap rowMap = moduleMap.get(bm);
					String type = rowMap.getString("SITRUE");// 是否动态计算单价模块
					double MKDJ = 0;// 期货价
					double TJ =0;// 体积
					double ZL = 0;// 重量
					double CLF = 0;// 期货价材料费
					double RGF = 0;// 人工费
					double WXWG = 0;// 外协费
					double CGF = 0;// 采购费
					/**
					 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
					 */
					List<BO> materials = SDK.getBOAPI().query("BO_EU_CPQ_MODULE_MSG_GRID")
							.bindId(rowMap.getString("BINDID")).list();
					Sheet sheet = workbookwl.createSheet("Sheet1");
					for (int i = 0; i < ps.size(); i++) {
						int num = i + 1;
						Cell cellB1 = sheet.createRow(i).createCell(1);
						//JSONObject jsonObject = (JSONObject) list.get(i);
						BO jsonObject =  ps.get(i);
						String bh =  jsonObject.getString("PARMENTIER_NO");
						JSONObject jsonObject2=	pMap.get(bh);
						String lx=jsonObject2.getString("type");
						if(lx.equals("0")) {
							cellB1.setCellValue(jsonObject2.getString("value"));
							System.out.println("cellB1:"+cellB1.getStringCellValue());
						}else {
							String value=jsonObject2.getString("value");
							double  numValue=Double.parseDouble(value);		
							 cellB1.setCellValue(numValue);
							 System.out.println("cellB1:"+cellB1.getNumericCellValue());
						}
						noMap.put(bh, "B$" + num);
					}
					// 如果是则进行动态计算
					if (true) {
						// 计算当前模块中物料明细的数量和物料的成本价格，根据物料明细的表中的计算规则进行计算。						
						/**
						 * 计算物料数量和成本。
						 */

						/************************** 通过excel公式进行计算 ***********************************/
					List<BO> materMap = new ArrayList<>();
						// 创建公式评估器
						// 设置单元格值
						
						// 将物料中的计算规则的参数替换为指定的excel参数
						if (materials.size() > 0) {
							for (int i = 0; i < materials.size(); i++) {
								BO bo = materials.get(i);
								String equation = bo.getString("WLJSGS");
								  Document document = Jsoup.parse(equation);
								  String newQuation=document.text();
								  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
									  System.out.println("参数计算公式："+newQuation);
										// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
										for (String key : pMap.keySet()) {
											int num = newQuation.indexOf(key);
											if (num > -1) {
												newQuation = newQuation.replace(key, noMap.get(key));
											}
										}
										System.out.println("转换后的参数计算公式："+newQuation);
										// 计算出物料数量和当前模块的物料成本价格
										FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
										Cell cellB1 = sheet.createRow(ps.size()+i).createCell(2);
										cellB1.setCellFormula(newQuation);
										formulaEvaluator.evaluateFormulaCell(cellB1);
										// 获取公式计算结果
										CellValue cellValue = formulaEvaluator.evaluate(cellB1);
										System.out.println("The result of the formula is: " + cellValue.getNumberValue());
										String price1 = bo.getString("DJ").isEmpty()?"0":bo.getString("DJ");
										String zl = bo.getString("ZL").isEmpty()?"0":bo.getString("ZL");
										String tj = bo.getString("TJ").isEmpty()?"0":bo.getString("TJ");
										String clf = bo.getString("CLF").isEmpty()?"0":bo.getString("CLF");
										String rgf = bo.getString("RGF").isEmpty()?"0":bo.getString("RGF");
										String wx = bo.getString("WXWG").isEmpty()?"0":bo.getString("WXWG");
										String cgf = bo.getString("CGF").isEmpty()?"0":bo.getString("CGF");
										double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();
										double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
										double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
										double clftotal1 = Double.parseDouble(clf) * cellValue.getNumberValue();										
										double rgftotal = Double.parseDouble(rgf) * cellValue.getNumberValue();
										double wxtotal = Double.parseDouble(wx) * cellValue.getNumberValue();
										double cgftotal = Double.parseDouble(cgf) * cellValue.getNumberValue();
										//System.out.println("物料名称:"+bo.getString("WLMC")+"单价："+price1+",总价："+total1);
										// 将数量和成本价格放入物料map集合中
										bo.set("SL", cellValue.getNumberValue());
										bo.set("ZJ", total1);
										bo.set("ZZL", zltotal);
										bo.set("ZTJ", tjtotal);
										bo.set("ZCGF", cgftotal);
										bo.set("ZGSF", rgftotal);
										bo.set("ZWX", wxtotal);
										bo.set("ZCLF",clftotal1);										
										
								  }
								  materMap.add(bo);
							}														
						}
						// 计算出模块的单价，每个物料总价相加
						double mkPrice1 = 0;						
						double zzlPrice = 0;
						double ztjPrice = 0;
						double zcgfPrice = 0;
						double zgsfPrice = 0;
						double zwxPrice = 0;
						double zclfPrice1 = 0;						
						for (BO bo : materMap) {
							String zjString1 = bo.getString("ZJ");							
							String zzlString = bo.getString("ZZL");
							String ztjString = bo.getString("ZTJ");
							String zcgfString = bo.getString("ZCGF");
							String zgsfString = bo.getString("ZGSF");
							String zwxString = bo.getString("ZWX");
							String zclfString1 = bo.getString("ZCLF");							
							mkPrice1 = mkPrice1 + Double.parseDouble(zjString1);							
							zzlPrice = zzlPrice + Double.parseDouble(zzlString);
							ztjPrice = ztjPrice + Double.parseDouble(ztjString);
							zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
							zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
							zwxPrice = zwxPrice + Double.parseDouble(zwxString);
							zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString1);		
							System.out.println(bo.getString("WLMC")+":mkPrice1:"+mkPrice1);
							/**
							 * 对原材料重量进行计算
							 * 
							 */
							if(aboutCPMap.containsKey(bo.getString("WLBM"))) {
								List<RowMap> lists=aboutCPMap.get(bo.getString("WLBM"));
								if(lists.size()>0) {
									String SL=bo.getString("SL");//数量
									BigDecimal sl=new BigDecimal(SL);
									for (RowMap cls : lists) {
										//System.out.println("物料编码："+bo.getString("WLBM")+"材料信息:"+cls.toString());
										String clbm=cls.getString("BM");//编码
										String clmc=cls.getString("MC");//名称
										String cllx=cls.getString("LX");//类型
										String clxh=cls.getString("XH");//型号
										String cldw=cls.getString("DW");//单位
										String clzl=cls.getString("ZL").isEmpty()?"0":cls.getString("ZL");//重量
										BigDecimal zl=new BigDecimal(clzl);
										BigDecimal addzl= sl.multiply(zl);
										if(clMaps.containsKey(clbm)) {
											JSONObject jsonObject=clMaps.get(clbm);
											String zlold=jsonObject.getString("ZL");
											BigDecimal zlOld= new BigDecimal(zlold);
											zlOld=zlOld.add(addzl);
											jsonObject.put("ZL", zlOld);
											clMaps.put(clbm, jsonObject);
										}else {
											JSONObject jsonObject=new JSONObject();
											jsonObject.put("BM",clbm );
											jsonObject.put("MC",clmc );
											jsonObject.put("LX",cllx);
											jsonObject.put("XH",clxh);
											jsonObject.put("DW",cldw );
											jsonObject.put("ZL",addzl );
											clMaps.put(clbm, jsonObject);
										}
									}
								}
							}
						}

							MKDJ = mkPrice1;							
							TJ = ztjPrice;
							ZL = zzlPrice;
							CLF = zclfPrice1;
							RGF = zgsfPrice;
							WXWG = zwxPrice;
							CGF = zcgfPrice;
							
						
						/************************** 通过excel公式进行计算 ***********************************/
					}
					/**
					 * 计算直发物资数量
					 */
					List<BO> zfBos = SDK.getBOAPI().query("BO_EU_BO_EU_CPQ_MODULE_MSG_STRAIGHT_HAIR")
							.bindId(rowMap.getString("BINDID")).list();
					double ZFYF = 0;// 直发物资运费价格
					if(zfBos.size()>0) {
						for (int i = 0; i < zfBos.size(); i++) {
							 BO bo2=zfBos.get(i);
							 String sljsgs=bo2.getString("JSGS");//直发运费数量计算公式；
								String yfdj=bo2.getString("YFBZ").isEmpty()?"0":bo2.getString("YFBZ");//运费单价；
								  String SL=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费单价；
							    double    price=Double.parseDouble(yfdj) ;
							    //double sl =Double.parseDouble(SL);
		 	        			 //BigDecimal sl = new BigDecimal(SL);
		                		 // double zj =price*sl;
								//ZFYF=ZFYF+zj;
								Document document = Jsoup.parse(sljsgs);
								String newQuation=document.text();
								 if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
									  for (String key : pMap.keySet()) {
											int num = newQuation.indexOf(key);
											if (num > -1) {
												newQuation = newQuation.replace(key, noMap.get(key));
											}
										}
									  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
										Cell cellB1 = sheet.createRow(ps.size()+materials.size()+1+i).createCell(2);
										cellB1.setCellFormula(newQuation);
										formulaEvaluator.evaluateFormulaCell(cellB1);
										// 获取公式计算结果
										CellValue cellValue = formulaEvaluator.evaluate(cellB1);
										System.out.println("The result of the formula is: " + cellValue.getNumberValue());  
										ZFYF=ZFYF+cellValue.getNumberValue()*price;
										// String upSql= "update BO_EU_CPQ_MODULE_RECORD_STRAIGHT_HAIR set SL= '"+cellValue.getNumberValue()+"',YFJG= '"+cellValue.getNumberValue()*price+"'  where ID='"+bo2.getId()+"' ; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											//sqlLists.add(upSql);

							}else {
								String sl=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费数量;
								 double num=Double.parseDouble(sl);
								ZFYF=ZFYF+num*price;
							}
							
						}
					}
					MKDJ =MKDJ+ZFYF ;
					System.out.println("MKDJ:"+MKDJ+"ZFYF:"+ZFYF);
					String rule = rowMap.getString("SLJSGZ"); // 计算规则
					/**
					 * 计算模块数量。
					 */
					System.out.println("模块名称："+json.getString("XDXH")+"，编码："+bm+"rule:"+rule);
					if (!rule.equals("0") && !rule.isEmpty()) {
						Document document = Jsoup.parse(rule);
						  String newRule=document.text();
						Map<String, BO> materMap = new HashMap<>();
						System.out.println("模块计算规则新："+newRule);
						// 创建公式评估器
						//XSSFWorkbook workbookwl = new XSSFWorkbook();
						//XSSFWorkbook workbook = new XSSFWorkbook();
						FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
						// 设置单元格值							
						// 将物料中的计算规则的参数替换为指定的excel参数
						// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
						for (String key : pMap.keySet()) {
							int num = newRule.indexOf(key);
							if (num > -1) {
								newRule = newRule.replace(key, noMap.get(key));
							}
						}
							
						// 计算出物料数量和当前模块的物料成本价格
						Cell cellB1 = sheet.createRow(ps.size()+zfBos.size()+materials.size()+3).createCell(3);
						cellB1.setCellFormula(newRule);
						System.out.println("cellB1value:"+cellB1.getCellFormula());
						formulaEvaluator.evaluateFormulaCell(cellB1);
						// 获取公式计算结果
						CellValue cellValue = formulaEvaluator.evaluate(cellB1);
						System.out.println("The result of the formula is: " + cellValue.getNumberValue());
						double number = cellValue.getNumberValue();
						// 计算出模块数量后，需要计算出模块的管理费GLFY、制造费用ZZFY、体积TJ、重量ZL
						// 总成本价RLJ = mkPrice8;// 入库价
						double MKDJ_price1 = MKDJ;						
						double MKTotalPrice1 = number * MKDJ_price1;						
						double zlTotalPrice = number * ZL;
						double tjTotalPrice = number * TJ;
						double clfTotalPrice1 = number * CLF;						
						double rgfTotalPrice = number * RGF;
						double wxTotalPrice = number * WXWG;
						double cgfTotalPrice = number * CGF;	
						double zfTotalPrice = number * ZFYF;	
						// 将计算好的数据放入选择的子表数据中，并更新当前数据。
						if(boMap.containsKey(id)) {
							BO mbo=	boMap.get(id);
							mbo.set("DJ", MKDJ);// 模块单价
							
							mbo.set("SL", number);// 模块数量
							mbo.set("DW", rowMap.getString("DW"));// 模块单位
							mbo.set("GG", rowMap.getString("GG"));// 规格
							mbo.set("ZJ", MKTotalPrice1);// 模块总价
							
							mbo.set("ZZL", zlTotalPrice);// 总重量
							mbo.set("ZTJ", tjTotalPrice);// 模块总体积
							mbo.set("ZCLF", clfTotalPrice1);// 模块总材料费
							
							mbo.set("ZGSF", rgfTotalPrice);// 模块总人工费
							mbo.set("ZWX", wxTotalPrice);// 模块总外协
							mbo.set("ZCGF", cgfTotalPrice);// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费
							
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("XDXH", rowMap.getString("MKMC"));// 选定型号
							mbo.set("MKBH", rowMap.getString("MKBM"));// 模块编码
							mbo.set("BZ", rowMap.getString("BZ"));// 适用备注说明
							mbo.set("BBH", rowMap.getString("BBH"));// 版本号
							mbo.set("VERSION", rowMap.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("MKXH",json.getString("MKXH"));// 系统序号
							// mbo.set("CSSM", msg);//参数说明
							//SDK.getBOAPI().update("BO_EU_CPQ_PROJECT_MSG_GRIDS", mbo);
							String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ+"',SL='"+number+"',DW='"+rowMap.getString("DW")+"',GG='"+rowMap.getString("GG")+"',ZJ='"+String.format("%.2f", MKTotalPrice1)+"'"
							 		+ ",ZCLF='"+String.format("%.2f", clfTotalPrice1)+"',ZGSF='"+String.format("%.2f", rgfTotalPrice)+"',ZWX='"+String.format("%.2f", wxTotalPrice) +"',ZCGF='"+String.format("%.2f", cgfTotalPrice)+"',ZZFWZYF='"+String.format("%.2f", zfTotalPrice)+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',"
							 		+ "CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',XDXH='"+rowMap.getString("MKMC")+"',MKBH='"+rowMap.getString("MKBM")+"',XTMC='"+json.getString("XTMC")+"'"
							 	    + ",MKMC='"+json.getString("MKMC")+"',MKXH='"+json.getString("MKXH")+"' where ID='"+mbo.getId()+"' ; ";
								//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
								sqlList.add(upSql);
								 System.out.println("upsql:"+upSql);
						}else {
							BO mbo=	new BO();
							mbo.set("DJ", MKDJ);// 模块单价
							
							mbo.set("SL", number);// 模块数量
							mbo.set("DW", rowMap.getString("DW"));// 模块单位
							mbo.set("GG", rowMap.getString("GG"));// 规格
							mbo.set("ZJ", MKTotalPrice1);// 模块总价

							mbo.set("ZZL", zlTotalPrice);// 总重量
							mbo.set("ZTJ", tjTotalPrice);// 模块总体积
							mbo.set("ZCLF", clfTotalPrice1);// 模块总材料费
							
							mbo.set("ZGSF", rgfTotalPrice);// 模块总人工费
							mbo.set("ZWX", wxTotalPrice);// 模块总外协
							mbo.set("ZCGF", cgfTotalPrice);// 模块总采购费
							mbo.set("ZZFWZYF", zfTotalPrice);// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费							
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("ZFWZYF", ZFYF);// 模块采购费
							mbo.set("XDXH", rowMap.getString("MKMC"));// 选定型号
							mbo.set("MKBH", rowMap.getString("MKBM"));// 模块编码
							mbo.set("BZ", rowMap.getString("BZ"));// 适用备注说明
							mbo.set("BBH", rowMap.getString("BBH"));// 版本号
							mbo.set("VERSION", rowMap.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("MKXH",json.getString("MKXH"));// 系统序号
							// mbo.set("CSSM", msg);//参数说明
							//SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_GRIDS", mbo, bindId, userContext.getUID());
							sqlLists.add(mbo);
						}
                        /**
                         * 计算原材料重量
                         */
						for (String key:clMaps.keySet()) {
							
							JSONObject jsonObject=clMaps.get(key);
							System.out.println("材料信息："+jsonObject.toJSONString());
							String CLZL=jsonObject.getString("ZL");//重量
							BigDecimal zl=new BigDecimal(CLZL);
							BigDecimal sl=new BigDecimal(number);//模块数量
							BigDecimal addzl= sl.multiply(zl);
							if(zclMaps.containsKey(key)) {
								JSONObject jsoncl=zclMaps.get(key);
								String zlold=jsoncl.getString("ZL");
								BigDecimal zlOld= new BigDecimal(zlold);
								zlOld=zlOld.add(addzl);
								jsoncl.put("ZL", zlOld);
								zclMaps.put(key, jsoncl);	
							}else {
								jsonObject.put("ZL", addzl);
								zclMaps.put(key, jsonObject);
							}
							
						}
					}else {
						if(boMap.containsKey(id)) {
							BO mbo=	boMap.get(id);
							mbo.set("DJ", MKDJ);// 模块单价
							
							String slString=json.getString("SL");
							mbo.set("SL", json.getString("SL"));// 模块数量
							mbo.set("DW", json.getString("DW"));// 模块单位
							mbo.set("GG", json.getString("GG"));// 规格
							BigDecimal slNum=new BigDecimal(slString);
							BigDecimal djs1=new BigDecimal(String.format("%.2f", MKDJ));
							
							mbo.set("ZJ",slNum.multiply(djs1));// 模块总价
							
							BigDecimal zls=new BigDecimal(ZL);
							BigDecimal tjs=new BigDecimal(TJ);
							BigDecimal clfs1=new BigDecimal(String.format("%.2f", CLF));
							BigDecimal zf=new BigDecimal(String.format("%.2f", ZFYF));
							BigDecimal rgfs=new BigDecimal(String.format("%.2f", RGF));
							BigDecimal wxwgs=new BigDecimal(String.format("%.2f", WXWG));
							BigDecimal cgfs=new BigDecimal(String.format("%.2f", CGF));
							mbo.set("ZZL", slNum.multiply(zls));// 总重量
							mbo.set("ZTJ",slNum.multiply(tjs));// 模块总体积
							mbo.set("ZCLF", slNum.multiply(clfs1));// 模块总材料费
							
							mbo.set("ZGSF", slNum.multiply(rgfs));// 模块总人工费
							mbo.set("ZWX", slNum.multiply(wxwgs));// 模块总外协
							mbo.set("ZCGF", slNum.multiply(cgfs));// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费
							
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("XDXH", json.getString("XDXH"));// 选定型号
							mbo.set("MKBH", json.getString("MKBH"));// 模块编码
							mbo.set("BZ", json.getString("BZ"));// 适用备注说明
							mbo.set("BBH", json.getString("BBH"));// 版本号
							mbo.set("VERSION", json.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("MKXH",json.getString("MKXH"));// 系统序号
							// mbo.set("CSSM", msg);//参数说明
							//SDK.getBOAPI().update("BO_EU_CPQ_PROJECT_MSG_GRIDS", mbo);
							 String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ+"',SL='"+json.getString("SL")+"',DW='"+json.getString("DW")+"',GG='"+json.getString("GG")+"',ZJ='"+slNum.multiply(djs1)+"'"
							 		+ ",ZCLF='"+slNum.multiply(clfs1)+"',ZGSF='"+slNum.multiply(rgfs)+"',ZWX='"+ slNum.multiply(wxwgs)+"',ZCGF='"+slNum.multiply(cgfs)+"',ZZFWZYF='"+slNum.multiply(zf)+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',"
							 		+ "CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',XDXH='"+json.getString("XDXH")+"',MKBH='"+json.getString("MKBH")+"',XTMC='"+json.getString("XTMC")+"',MKMC='"+json.getString("MKMC")+"',MKXH='"+json.getString("MKXH")+"' where ID='"+mbo.getId()+"' ; ";
								//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
							 System.out.println("upsql----:"+upSql);
								sqlList.add(upSql);
							
							
							
						}else {
							BO mbo=	new BO();
							mbo.set("DJ", MKDJ);// 模块单价							
							mbo.set("SL", json.getString("SL"));// 模块数量
							mbo.set("DW", json.getString("DW"));// 模块单位
							mbo.set("GG", json.getString("GG"));// 规格
							mbo.set("ZJ", json.getString("ZJ"));// 模块总价							
							mbo.set("ZZL", json.getString("ZZL"));// 总重量
							mbo.set("ZTJ", json.getString("ZTJ"));// 模块总体积
							mbo.set("ZCLF", json.getString("ZCLF"));// 模块总材料费							
							mbo.set("ZGSF", json.getString("ZRGF"));// 模块总人工费
							mbo.set("ZWX", json.getString("ZWX"));// 模块总外协
							mbo.set("ZCGF", json.getString("ZCGF"));// 模块总采购费
							mbo.set("ZFWZYF", json.getString("ZFWZYF"));// 模块总采购费
							mbo.set("ZZFWZYF", json.getString("ZFWZYF"));// 模块总采购费
							mbo.set("ZL", ZL);// 重量
							mbo.set("TJ", TJ);// 模块体积
							mbo.set("CLF", CLF);// 模块材料费							
							mbo.set("RGF", RGF);// 模块人工费
							mbo.set("WXWG", WXWG);// 模块外协
							mbo.set("CGF", CGF);// 模块采购费
							mbo.set("XDXH", json.getString("XDXH"));// 选定型号
							mbo.set("MKBH", json.getString("MKBH"));// 模块编码
							mbo.set("BZ", json.getString("BZ"));// 适用备注说明
							mbo.set("BBH", json.getString("BBH"));// 版本号
							mbo.set("VERSION", json.getString("VERSION"));// 版本
							mbo.set("XTMC", json.getString("XTMC"));// 系统名称
							mbo.set("MKMC", json.getString("MKMC"));// 模块名称
							mbo.set("MKXH",json.getString("MKXH"));// 系统序号
							//SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_GRIDS", mbo, bindId, userContext.getUID());
							sqlLists.add(mbo);
							
						}
						//计算模块原材料重量
						/**
                         * 计算原材料重量
                         */
						for (String key:clMaps.keySet()) {
							JSONObject jsonObject=clMaps.get(key);
							System.out.println("材料信息："+jsonObject.toJSONString());
							String CLZL=jsonObject.getString("ZL");//重量
							BigDecimal zl=new BigDecimal(CLZL);
							String SL=json.getString("SL").isEmpty()?"0":json.getString("SL");
							BigDecimal sl=new BigDecimal(SL);//模块数量
							BigDecimal addzl= sl.multiply(zl);
							if(zclMaps.containsKey(key)) {
								JSONObject jsoncl=zclMaps.get(key);
								String zlold=jsoncl.getString("ZL");
								BigDecimal zlOld= new BigDecimal(zlold);
								zlOld=zlOld.add(addzl);
								jsoncl.put("ZL", zlOld);
								zclMaps.put(key, jsoncl);	
							}else {
								jsonObject.put("ZL", addzl);
								zclMaps.put(key, jsonObject);
							}
							
						}
						
					}
					
				}
				
			} catch (Exception e) {
				throw e;
			}finally {
				try {
					workbookwl.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	  			 try {
					workbook.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		
			}
		}

               if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
					 DBSql.batch(array3);
				}
				if(sqlLists.size()>0) {
					SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_GRIDS", sqlLists, bindId, userContext.getUID()) ;
				}	
		
		
		//将计算出来的原材料重量数据存放到项目申请单中
		if(zclMaps.size()>0) {
			SDK.getBOAPI().removeByBindId("BO_EU_CPQ_PROJECT_MSG_MATERIAL‌", bindId);
			List<BO> cls=SDK.getBOAPI().query("BO_EU_CPQ_PROPERTIES").list();
			Map<String, BO> clMap = new HashMap<String, BO>();
			if(cls.size()>0) {
				for (int i = 0; i < cls.size(); i++) {
					//JSONObject json = (JSONObject) list.get(i);
					BO json =  cls.get(i);
					clMap.put(json.getString("CLBM"), json);
					
				}
			}
			List<BO> bos2=new ArrayList<>();
			for (String key : zclMaps.keySet()) {
				BO bo=new BO();
				JSONObject json=zclMaps.get(key);
				bo.set("CLBM", json.getString("BM"));
				bo.set("CLMC", json.getString("MC"));
				bo.set("LX", json.getString("LX"));
				bo.set("GG", json.getString("XH"));
				//bo.set("DW", json.getString("DW"));
				BigDecimal zl=new BigDecimal(json.getString("ZL").isEmpty()?"0":json.getString("ZL"));
				BigDecimal rounded = zl.setScale(0, RoundingMode.HALF_UP);
				if(clMap.containsKey(key)) {
					BO bo2=clMap.get(key);
				String dj=	bo2.getString("BHSCGJ");
				bo.set("QHJ", dj);
				String rkj=	bo2.getString("RKJ");
				String lsj=	bo2.getString("LSJ");
				if(!rkj.isEmpty()) {
					BigDecimal dj1 = new BigDecimal(dj);
					BigDecimal rkj1 = new BigDecimal(rkj);
					if(rounded.compareTo(BigDecimal.ZERO)==0) {
						bo.set("RKJCE", "0");
					}else {
						BigDecimal rkcj=dj1.subtract(rkj1).multiply(rounded);
						bo.set("RKJCE", rkcj);
					}										
					bo.set("RKJ", rkj);
					
				}else {
					bo.set("RKJ", "0");
					bo.set("RKJCE", "0");
				}
				if(!lsj.isEmpty()) {
					BigDecimal dj1 = new BigDecimal(dj);
					BigDecimal lsj1 = new BigDecimal(lsj);
					if(rounded.compareTo(BigDecimal.ZERO)==0) {
						bo.set("LSJCE", "0");
					}else {
						BigDecimal lscj=dj1.subtract(lsj1).multiply(rounded);
						bo.set("LSJCE", lscj);
					}
					
					bo.set("LSJ", lsj1);
					
				}else {
					bo.set("LSJ", "0");
					bo.set("LSJCE", "0");
				}
				}
				bo.set("CLZL", rounded);
				//System.out.println("汇总材料信息："+bo.toString());
				bos2.add(bo);
			}
			SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_MATERIAL‌", bos2, bindId, userContext.getUID());
		}
		
		
		
		ResponseObject ro = ResponseObject.newOkResponse();
		return ro.toString();
	}

}
